package com.jietu.mapper;

import com.jietu.entity.Vo;
import com.jietu.entity.Vo1;
import com.jietu.entity.Vo2;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @author LQ&LYPlq
 * @ClassName VoMapper
 * @description: TODO
 * @date 2024年12月24日
 * @version: 1.0
 */
@Mapper
public interface VoMapper {
    /**
     * 1.1流量分时统计折线图进行展示（新访客数）
     */
    @Select("select   \n" +
            "\t hour(user_chapter_process.create_time) name,\n" +
            "   count(*) value \n" +
            "\t \t\tfrom    user_chapter_process   \n" +
            "\t\t  group  by     hour(user_chapter_process.create_time)  \n" +
            "\t\t\torder by   hour(user_chapter_process.create_time)  asc")
   public  List<Vo> list1();
    /**
     * 1.2答题情况统计轮播表格显示
     */
    @Select("select  test_question_info.question_txt    name ,\n" +
            "\t      count(test_exam_question.id)   value  ,\n" +
            "\t\t\t\tsum(test_exam_question.is_correct)  totalNum , \n" +
            "\t\t\t\tsum(test_exam_question.is_correct)/count(test_exam_question.id)  avgNum \n" +
            "\t      from  test_exam_question\n" +
            "\t\t  left   join    test_question_info  \n" +
            "\t\t\ton  test_question_info.id=test_exam_question.question_id  group  by    test_question_info.question_txt")
    public  List<Vo1> list2();
    /**
     *1.3各试卷考试统计
     */
    @Select("select  \n" +
            "\t    test_paper.paper_title name,\n" +
            "\t\t\tcount(test_exam.user_id) value,\n" +
            "\t\t\tsum(test_exam.score)/count(test_exam.user_id)  avgNum,\n" +
            "\t\t\tsum(test_exam.duration_sec)/count(test_exam.user_id)  avgTime\n" +
            "\t\t\tfrom  test_exam  \n" +
            "      left  join  test_paper   on  test_exam.paper_id=test_paper.id\n" +
            "      group  by     test_paper.paper_title")
    public  List<Vo2> list3();
    /**
     *1.4各来源流量统计
     */
    @Select("select    \n" +
            "\t          base_source.source_site name ,\n" +
            "\t          sum(order_info.id)  value       \n" +
            "\t          from    base_source   \n" +
            "\t          left  join  video_info  on  base_source.id=video_info.video_source_id \n" +
            "\t\t\t\t\t\tleft  join  chapter_info on  chapter_info.id=video_info.chapter_id\n" +
            "\t\t\t\t\t\tleft  join  course_info  on   course_info.id=chapter_info.course_id\n" +
            "\t\t\t\t\t\tleft   join  order_detail  on   order_detail.course_id=course_info.id\n" +
            "\t\t\t\t\t\tleft  join   order_info   on    order_info.id=order_detail.order_id\n" +
            "\t\t\t\t\t\tgroup  by   base_source.source_site")
    public  List<Vo> list4();



}
